﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace TLTuan.App.AccessLayer
{
    public class CConnection
    {
        static string _str_conn = (string)ConfigurationSettings.AppSettings["ConnectionString"];
        static SqlConnection _conn;

        /// <summary>
        /// Tao Connection va Open
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetConnection()
        {
            if (_conn == null)
            {
                _conn = new SqlConnection(_str_conn);
                _conn.Open();
                return _conn;
            }
            else
            {
                if (_conn.State != ConnectionState.Open)
                    _conn.Open();
                return _conn;
            }
        }

        public static bool Close()
        {
            try
            {
                if (_conn.State == System.Data.ConnectionState.Open)
                {
                    _conn.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetData(string ip_sql, params Object[] ip_params)
        {
            var v_sql_command = CreateCommand(ip_sql, ip_params);
            var v_sql_adapter = new SqlDataAdapter(v_sql_command);
            var v_dtable = new DataTable();
            v_sql_adapter.Fill(v_dtable);
            CConnection.Close();
            return v_dtable;
        }

        public static int ExecuteNonQuery(string ip_sql, params object[] parameters)
        {
            SqlCommand v_comm = CreateCommand(ip_sql, parameters);

            int v_row = v_comm.ExecuteNonQuery();

            CConnection.Close();
            return v_row;
        }

        #region Method ...

        private static SqlCommand CreateCommand(string ip_sql, object[] parameters)
        {
            SqlConnection v_conn = CConnection.GetConnection();
            SqlCommand v_comm = new SqlCommand(ip_sql, v_conn);
            v_comm.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < parameters.Length; i += 2)
            {
                v_comm.Parameters.AddWithValue(parameters[i].ToString(), parameters[i + 1]);
            }
            return v_comm;
        }

        #endregion
    }
}